﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using Entities;

namespace Data
{
   public class SachRepository
   {
       
       public static List<SachDTO> GetSachList()
       {
           
           NhaSachEntities context = new NhaSachEntities();
           
           var q = (from s in context.SachList
                   where s.TinhTrang==1
                   select new SachDTO { 
                       MaSach=s.MaSach,
                       TenSach=s.TenSach,
                       MaTheLoai=(int)s.TheLoai.MaTheLoai,
                       MaTacGia=s.TacGia.MaTacGia,
                       SoLuong=(int)s.SoLuong,
                       DonGia=(double)s.DonGia,
                       TinhTrang=(int)s.TinhTrang

                   }).DefaultIfEmpty();
           return q.ToList();
       }
       public static List<SachDTO> GetSachDeletedList()
       {
           NhaSachEntities context = new NhaSachEntities();

           var q = (from s in context.SachList
                    where s.TinhTrang == 0
                    select new SachDTO
                    {
                        MaSach = s.MaSach,
                        TenSach = s.TenSach,
                        MaTheLoai = (int)s.TheLoai.MaTheLoai,
                        MaTacGia = s.TacGia.MaTacGia,
                        SoLuong = (int)s.SoLuong,
                        DonGia = (double)s.DonGia,
                        TinhTrang = (int)s.TinhTrang

                    }).DefaultIfEmpty();
           return q.ToList();
       }
       public static List<SachDTO> GetSachList(int skip, int take)
       {
           NhaSachEntities context = new NhaSachEntities();

           var q = (from s in context.SachList
                    where s.TinhTrang == 1
                    orderby s.MaSach
                    select new SachDTO
                    {
                        MaSach = s.MaSach,
                        TenSach = s.TenSach,
                        MaTheLoai = (int)s.TheLoai.MaTheLoai,
                        MaTacGia = s.TacGia.MaTacGia,
                        SoLuong = (int)s.SoLuong,
                        DonGia = (double)s.DonGia,
                        TinhTrang = (int)s.TinhTrang

                    }).Skip(skip).Take(take).DefaultIfEmpty(); 
           return q.ToList();
       }
       public static SachDTO GetSachByMaSach(int maSach)
       {
           NhaSachEntities context = new NhaSachEntities();
           var q = (from s in context.SachList
                    where s.MaSach == maSach
                    select new SachDTO()
                    {
                        MaSach =(int) s.MaSach,
                        TenSach = s.TenSach,
                        MaTheLoai = (int)s.TheLoai.MaTheLoai,
                        MaTacGia = (int)s.TacGia.MaTacGia,
                        SoLuong = (int)s.SoLuong,
                        DonGia = (double)s.DonGia,
                        TinhTrang = (int)s.TinhTrang
                    }).FirstOrDefault();


           return q;
       }
       public static List<SachDTO> SearchByTenSach(String tenSach)
       {
           
           NhaSachEntities context=new NhaSachEntities();
           var q = from s in context.SachList
                    where s.TenSach.Contains(tenSach) && s.TinhTrang==1
                    select new SachDTO()
                    {
                        MaSach = (int)s.MaSach,
                        TenSach = s.TenSach,
                        MaTheLoai = (int)s.TheLoai.MaTheLoai,
                        MaTacGia = (int)s.TacGia.MaTacGia,
                        SoLuong = (int)s.SoLuong,
                        DonGia = (double)s.DonGia,
                        TinhTrang = (int)s.TinhTrang
                    };
           return q.ToList();
       }
       public static List<SachDTO> Search(string tenSach, string tenTheLoai, string tenTacGia, double gia, int loaiGia, int soLuong, int loaiSoLuong)
       {
           NhaSachEntities context=new NhaSachEntities();
           var sach = (from s in context.SachList
                       join tl in context.TheLoaiList on s.MaTheLoai equals tl.MaTheLoai
                       join tg in context.TacGiaList on s.MaTacGia equals tg.MaTacGia
                       where s.TinhTrang==1 && s.TenSach.Contains(tenSach) 
                            && tl.TenTheLoai.Contains(tenTheLoai)
                             && tg.TenTacGia.Contains(tenTacGia)                       
                             && (loaiGia == 0 ? s.DonGia == gia : loaiGia < 0 ? s.DonGia < gia : s.DonGia > gia)
                             && (loaiSoLuong == 0 ? s.SoLuong == soLuong : loaiSoLuong < 0 ? s.SoLuong < soLuong : s.SoLuong > soLuong)
                       select new SachDTO()
                       {
                           MaSach = (int)s.MaSach,
                           TenSach = s.TenSach,
                           MaTheLoai = (int)s.TheLoai.MaTheLoai,
                           MaTacGia = (int)s.TacGia.MaTacGia,
                           SoLuong = (int)s.SoLuong,
                           DonGia = (double)s.DonGia,
                           TinhTrang = (int)s.TinhTrang
                       }
                        ).DefaultIfEmpty().Distinct();
           return sach.ToList();           
       }


       public static List<SachDTO> Search(string tenSach, string tenTheLoai, string tenTacGia, double gia, int loaiGia)
       {
           NhaSachEntities context = new NhaSachEntities();
           var sach = (from s in context.SachList
                       join tl in context.TheLoaiList on s.MaTheLoai equals tl.MaTheLoai
                       join tg in context.TacGiaList on s.MaTacGia equals tg.MaTacGia
                       where s.TinhTrang == 1 && s.TenSach.Contains(tenSach)
                            && tl.TenTheLoai.Contains(tenTheLoai)
                             && tg.TenTacGia.Contains(tenTacGia)
                             && (loaiGia == 0 ? s.DonGia == gia : loaiGia < 0 ? s.DonGia < gia : s.DonGia > gia)

                       select new SachDTO()
                       {
                           MaSach = (int)s.MaSach,
                           TenSach = s.TenSach,
                           MaTheLoai = (int)s.TheLoai.MaTheLoai,
                           MaTacGia = (int)s.TacGia.MaTacGia,
                           SoLuong = (int)s.SoLuong,
                           DonGia = (double)s.DonGia,
                           TinhTrang = (int)s.TinhTrang
                       }
                        ).DefaultIfEmpty().Distinct();
           return sach.ToList();
       }

       public static List<SachDTO> Search(string tenSach, string tenTheLoai, string tenTacGia,  int soLuong, int loaiSoLuong)
       {
           NhaSachEntities context = new NhaSachEntities();
           var sach = (from s in context.SachList
                       join tl in context.TheLoaiList on s.MaTheLoai equals tl.MaTheLoai
                       join tg in context.TacGiaList on s.MaTacGia equals tg.MaTacGia
                       where s.TinhTrang == 1 && s.TenSach.Contains(tenSach)
                            && tl.TenTheLoai.Contains(tenTheLoai)
                             && tg.TenTacGia.Contains(tenTacGia)
                             && (loaiSoLuong == 0 ? s.SoLuong == soLuong : loaiSoLuong < 0 ? s.SoLuong < soLuong : s.SoLuong > soLuong)
                       select new SachDTO()
                       {
                           MaSach = (int)s.MaSach,
                           TenSach = s.TenSach,
                           MaTheLoai = (int)s.TheLoai.MaTheLoai,
                           MaTacGia = (int)s.TacGia.MaTacGia,
                           SoLuong = (int)s.SoLuong,
                           DonGia = (double)s.DonGia,
                           TinhTrang = (int)s.TinhTrang
                       }
                        ).DefaultIfEmpty().Distinct();
           return sach.ToList();
       }
       public static List<SachDTO> Search(string tenSach, string tenTheLoai, string tenTacGia)
       {
           NhaSachEntities context = new NhaSachEntities();
           var sach = (from s in context.SachList
                       join tl in context.TheLoaiList on s.MaTheLoai equals tl.MaTheLoai
                       join tg in context.TacGiaList on s.MaTacGia equals tg.MaTacGia
                       where s.TinhTrang == 1 && s.TenSach.Contains(tenSach)
                            && tl.TenTheLoai.Contains(tenTheLoai)
                            && tg.TenTacGia.Contains(tenTacGia)
                       select new SachDTO()
                       {
                           MaSach = (int)s.MaSach,
                           TenSach = s.TenSach,
                           MaTheLoai = (int)s.TheLoai.MaTheLoai,
                           MaTacGia = (int)s.TacGia.MaTacGia,
                           SoLuong = (int)s.SoLuong,
                           DonGia = (double)s.DonGia,
                           TinhTrang = (int)s.TinhTrang
                       }
                        ).DefaultIfEmpty().Distinct();
           return sach.ToList();
       }

       public static int GetCurrentID()
       {
           NhaSachEntities context=new NhaSachEntities();
           Nullable<int> id = context.GetCurrentID("Sach").SingleOrDefault();
           return (int)id;
       }
       public static void Insert(SachDTO sach)
       {
           NhaSachEntities context = new NhaSachEntities();
           Sach s = new Sach()
           {
               MaSach=sach.MaSach,
               TenSach=sach.TenSach,
               MaTacGia=sach.MaTacGia,
               MaTheLoai=sach.MaTheLoai,
               SoLuong=sach.SoLuong,
               DonGia=sach.DonGia,
               TinhTrang=sach.TinhTrang
           };
           context.AddToSachList(s);
           context.SaveChanges();
       }

       public static void Update(SachDTO sach)
       {
           NhaSachEntities context = new NhaSachEntities();
           var q = (from s in context.SachList
                    where s.MaSach == sach.MaSach
                    select s).SingleOrDefault();
           q.TenSach = sach.TenSach;
           q.MaTheLoai = sach.MaTheLoai;
           q.MaTacGia = sach.MaTacGia;
           q.SoLuong = sach.SoLuong;
           q.DonGia = sach.DonGia;
           q.TinhTrang = sach.TinhTrang;

           context.SaveChanges();
       }

       public static void Delete(int maSach)
       {
           NhaSachEntities context = new NhaSachEntities();
           var sach = (from s in context.SachList
                       where s.MaSach == maSach
                       select s).SingleOrDefault();
           context.DeleteObject(sach);
           context.SaveChanges();
       }

       public static void SetDelete(int maSach)
       {
           NhaSachEntities context = new NhaSachEntities();
           var sach = (from s in context.SachList
                       where s.MaSach == maSach
                       select s).SingleOrDefault();
           sach.TinhTrang = 0;
           context.SaveChanges();
       }

       public static bool CheckDelete(int maSach)
       {
           
           NhaSachEntities context = new NhaSachEntities();

           var chiTietHoaDon=(from cthd in context.ChiTietHoaDonList
                              where cthd.MaSach==maSach
                                  select cthd).Count();
           if(chiTietHoaDon>0)
               return false;


           var chiTietBaoCaoTon=(from ctbc in context.ChiTietBaoCaoTonList
                                 where ctbc.MaSach==maSach
                                     select ctbc).Count();
           if(chiTietBaoCaoTon>0)
               return false;

           var chiTietPhieuNhap=(from ctpn in context.ChiTietPhieuNhapList
                                 where ctpn.MaSach==maSach
                                     select ctpn).Count();
           if(chiTietPhieuNhap>0)
               return false;
           return true;
       }

       public static void Undelete(int maSach)
       {
           NhaSachEntities context = new NhaSachEntities();
           var sach = (from s in context.SachList
                       where s.MaSach == maSach
                       select s).SingleOrDefault();
           sach.TinhTrang = 1;
           context.SaveChanges();
       }
    }
}
